package com.iflytek.autoCompareTool.utils;

import com.iflytek.autoCompareTool.model.TestSet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author llchen12
 * @date 2018/5/31
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);


    public static List<TestSet> readCustomTestSet(String filePath, int type, String fileName, Integer id) {

        List<TestSet> testSetList = new ArrayList<>();
        String fileNameStr;
        if (filePath.contains("\\")) {
            fileNameStr = filePath.substring(filePath.lastIndexOf("\\") + 1);
        } else {
            fileNameStr = filePath.substring(filePath.lastIndexOf("/") + 1);
        }

        String fileId = filePath.replace(filePath.substring(filePath.indexOf("("), filePath.lastIndexOf(")")+1), "");

        try (InputStream is = FastdfsUtil.downloadToStream(fileId)) {

            Workbook wb;
            if (fileId.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = new HSSFWorkbook(is);
            }

            String appid;
            String parameter;
            String usrid;
            String uid = null;
            String userData;
            String text;

            Sheet sheet = wb.getSheetAt(0);
            int rowSize = sheet.getLastRowNum() + 1;

            if (rowSize > 2) {
                //统计连续多少行为空
                int blank = 0;
                //记录是否为连续行
                int index = 0;

                Cell cell;
                //循环读取所有列表,从第二行开始 appid和text不能为空
                for (int i = 1; i < rowSize; i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    //读取appid
                    cell = row.getCell(0);
                    if (cell == null) {
                        logger.error("{} 第 {} 行appid无法识别", fileNameStr, i + 1);
                        //统计连续多少行为空
                        if (index == i - 1) {
                            blank++;
                        } else {
                            blank = 0;
                        }
                        //连续5行为空时，停止读取后续内容
                        if (blank == 4) {
                            break;
                        }
                        index = i;
                        continue;
                    } else {
                        appid = getValue(cell);
                    }

                    //读取usrid
                    cell = row.getCell(1);
                    if (cell == null) {
                        usrid = null;
                    } else {
                        usrid = cell.toString();
                    }

                    //读取参数
                    cell = row.getCell(2);
                    if (cell == null ) {
                        parameter = null;
                    } else {
                        parameter = cell.toString();
                    }

                    //读取uid
                    cell = row.getCell(3);
                    if (cell == null ) {
                        parameter = null;
                    } else {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            //防止变为科学计数
                            DecimalFormat df = new DecimalFormat("0");
                            uid = df.format(cell.getNumericCellValue()) + "_" + id;
                        } else {
                            uid = cell.toString() + "_" + id;
                        }
                    }
                    //读取user_data **/
                    cell = row.getCell(4);
                    if (cell == null ) {
                        userData = null;
                    } else {
                        userData = cell.toString();
                    }

                    //读取text **/
                    cell = row.getCell(5);
                    if (cell == null ) {
                        //统计连续多少行为空
                        if (index == i - 1) {
                            blank++;
                        } else {
                            blank = 0;
                        }
                        //连续5行为空时，停止读取后续内容
                        if (blank == 4) {
                            break;
                        }
                        index = i;
                        logger.error(fileNameStr + "第" + (i + 1) + "行text无法识别");
                        continue;
                    } else {
                        text = cell.toString();
                    }

                    //生成测试集

                    TestSet testSet = new TestSet();
                    testSet.setAppid(appid);
                    testSet.setUsrid(usrid);
                    testSet.setParameter(parameter);
                    testSet.setUid(uid);
                    testSet.setUserData(userData);
                    testSet.setText(text);
                    testSet.setNum(i);
                    testSet.setFileName(fileName);

                    //如果需要和标注集对比，需要继续读取标注集信息

                    if (type == 1) {
                        String service;
                        String semantic;
                        String operation;
                        String answer;

                        //读取标注业务 **/
                        cell = row.getCell(6);
                        if (cell == null) {
                            service = null;
                        } else {
                            service = cell.toString();
                        }
                        //读取标注结果 **/
                        cell = row.getCell(7);
                        if (cell == null) {
                            semantic = null;
                        } else {
                            semantic = cell.toString();
                        }
                        //读取标注操作 **/
                        cell = row.getCell(8);
                        if (cell == null) {
                            operation = null;
                        } else {
                            operation = cell.toString();
                        }
                        //读取标注answer **/
                        cell = row.getCell(9);
                        if (cell == null ) {
                            answer = null;
                        } else {
                            answer = cell.toString();
                        }

                        testSet.setSemantic(semantic);
                        testSet.setService(service);
                        testSet.setOperation(operation);
                        testSet.setAnswer(answer);
                    }

                    testSetList.add(testSet);
                }
            }

        } catch (Exception e) {
            logger.error("读取excel异常", e);
        }

        return testSetList;
    }

    private static String getValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cellType == CellType.NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(cell.getNumericCellValue());
        } else {
            return cell.getStringCellValue();
        }
    }


}
